/* SQL Document */


USE PEDIDOS;


-- EXEMPLO 1 (PROCEDURE)

CREATE PROCEDURE PROC_TESTE1
AS 
BEGIN
SELECT * FROM PEDIDOS.dbo.CLIENTES
END

EXEC PROC_TESTE1;


-- EXEMPLO 2 (PROCEDURE)

select @@NESTLEVEL;

CREATE PROCEDURE PROC_A
AS BEGIN
PRINT 'PROC_A  -  ' + CAST( @@NESTLEVEL AS VARCHAR(2) );
EXEC PROC_B;
PRINT 'VOLTEI PARA PROC_A'
END

CREATE PROCEDURE PROC_B
AS BEGIN
PRINT 'PROC_B  -  ' + CAST( @@NESTLEVEL AS VARCHAR(2) );
EXEC PROC_C;
PRINT 'VOLTEI PARA PROC_B'
END

CREATE PROCEDURE PROC_C
AS BEGIN
PRINT 'PROC_C  -  ' + CAST( @@NESTLEVEL AS VARCHAR(2) );
END

EXEC PROC_A;
EXEC PROC_B;
EXEC PROC_C;



select @@nestlevel



-- EXEMPLO 3 (PROCEDURE COM INPUT)

CREATE PROCEDURE PROC_TESTE2 @CODIGO INT
AS BEGIN
SELECT C.CODCLI , C.NOME FROM PEDIDOS.dbo.CLIENTES C WHERE C.CODCLI = @CODIGO
END

EXEC PROC_TESTE2 5;
EXEC PROC_TESTE2 7;
EXEC PROC_TESTE2 799;

DROP PROCEDURE PROC_TESTE2;



-- EXEMPLO 4 (PROCEDURE COM INPUT)

CREATE PROCEDURE PROC_TESTE3 @CODIGO INT
AS BEGIN
SELECT     TOP 1 WITH TIES CLIENTES.NOME, PEDIDOS.NUM_PEDIDO, PEDIDOS.DATA_EMISSAO
FROM         CLIENTES INNER JOIN
                      PEDIDOS ON CLIENTES.CODCLI = PEDIDOS.CODCLI
                      WHERE CLIENTES.CODCLI = @CODIGO
ORDER BY PEDIDOS.DATA_EMISSAO DESC
END


EXEC PROC_TESTE3 11




---------------------------------------------------------------
USE PEDIDOS;

-- 1. (COMPARANDO COM VIEW)
CREATE PROCEDURE STP_TOT_VENDIDO @ANO INT
AS BEGIN
SELECT MONTH( DATA_EMISSAO ) AS MES,
       YEAR( DATA_EMISSAO ) AS ANO,
       SUM( VLR_TOTAL ) AS TOT_VENDIDO
FROM PEDIDOS
 WHERE YEAR(DATA_EMISSAO) = @ANO
GROUP BY MONTH(DATA_EMISSAO), YEAR(DATA_EMISSAO)
ORDER BY MES
END



--- Testando
EXEC STP_TOT_VENDIDO 2005
EXEC STP_TOT_VENDIDO 2006
EXEC STP_TOT_VENDIDO 2007


EXEC STP_TOT_VENDIDO @ANO = 2007


-- OU 
CREATE VIEW VTESTE AS
SELECT MONTH( DATA_EMISSAO ) AS MES,
       YEAR( DATA_EMISSAO ) AS ANO,
       SUM( VLR_TOTAL ) AS TOT_VENDIDO
FROM PEDIDOS
GROUP BY YEAR(DATA_EMISSAO) , MONTH(DATA_EMISSAO)


SELECT * FROM VTESTE;








-- 2.(PROCEDURE COM DIVERSOS INPUTS)
CREATE PROCEDURE STP_ITENS_PEDIDO	@DT1 DATETIME,
									@DT2 DATETIME,
									@CLIENTE VARCHAR(40) = '%', 
									@VENDEDOR VARCHAR(40) = '%'
AS BEGIN
SELECT 
  I.NUM_PEDIDO, I.NUM_ITEM, I.ID_PRODUTO, I.COD_PRODUTO,
  I.QUANTIDADE, I.PR_UNITARIO, I.DESCONTO, I.DATA_ENTREGA,
  PE.DATA_EMISSAO, PR.DESCRICAO, C.NOME AS CLIENTE,
  V.NOME AS VENDEDOR
FROM PEDIDOS PE
  JOIN CLIENTES C ON PE.CODCLI = C.CODCLI
  JOIN VENDEDORES V ON PE.CODVEN = V.CODVEN
  JOIN ITENSPEDIDO I ON PE.NUM_PEDIDO = I.NUM_PEDIDO
  JOIN PRODUTOS PR ON I.ID_PRODUTO = PR.ID_PRODUTO
WHERE PE.DATA_EMISSAO BETWEEN @DT1 AND @DT2 AND
      C.NOME LIKE @CLIENTE AND V.NOME LIKE @VENDEDOR
ORDER BY I.NUM_PEDIDO
END

-- TESTANDO


EXEC STP_ITENS_PEDIDO '2007/01/01' , '2007/10/10';

EXEC STP_ITENS_PEDIDO '2007/01/01' , '2007/10/10' , '%' , 'C%'

EXEC STP_ITENS_PEDIDO '2007.1.1', '2007.1.31', '%BRINDES%', 'LEIA'


-- Omitindo o nome do vendedor
EXEC STP_ITENS_PEDIDO '2007.1.1', '2007.1.31', '%BRINDES%'


-- Omitindo o nome do vendedor e do cliente
EXEC STP_ITENS_PEDIDO '2007.1.1', '2007.1.31'

EXEC STP_ITENS_PEDIDO @DT1 = '2007.1.1', 
                      @DT2 = '2007.1.31',
                      @VENDEDOR = 'LEIA%'
                      
                      
                      
                     
  
-- 3. (PROCEDURE COM RETORNO)     
CREATE PROCEDURE STP_ULT_DATA_COMPRA @CODCLI INT
AS BEGIN
IF NOT EXISTS( SELECT * FROM PEDIDOS
               WHERE CODCLI = @CODCLI )
   RETURN -1;
               
SELECT MAX(DATA_EMISSAO) AS ULT_DATA_COMPRA 
FROM PEDIDOS WHERE CODCLI = @CODCLI;
END       
               
-- Teste 1
DECLARE @RET INT;
EXEC @RET = STP_ULT_DATA_COMPRA 3
IF @RET < 0 PRINT 'NÃO EXISTE PEDIDO DESTE CLIENTE'


-- Teste 2
DECLARE @RET INT;
EXEC @RET = STP_ULT_DATA_COMPRA 1
IF @RET < 0 PRINT 'NÃO EXISTE PEDIDO DESTE CLIENTE'




-- 

CREATE PROCEDURE STP_PEGA_RETORNO
AS BEGIN
	DECLARE @NUM INT = CAST(RAND() * 75 AS INT);
	RETURN @NUM;
END


-- TESTES

-- #1
EXEC STP_PEGA_RETORNO; 

-- #2


DECLARE @OLHA INT
EXEC @OLHA = STP_PEGA_RETORNO;
PRINT @OLHA;

-- #3

PRINT STP_PEGA_RETORNO; -- Não funciona!





-- 4. 
CREATE PROCEDURE STP_COPIA_PRODUTO @ID_PRODUTO INT
AS BEGIN
DECLARE @ID_PRODUTO_NOVO INT;
-- Copia o registro existente para um novo registro
INSERT INTO PRODUTOS
( COD_PRODUTO, DESCRICAO, COD_UNIDADE, COD_TIPO, PRECO_CUSTO,
  PRECO_VENDA, QTD_ESTIMADA, QTD_REAL, QTD_MINIMA, CLAS_FISC,
  IPI, PESO_LIQ )
SELECT COD_PRODUTO, DESCRICAO, COD_UNIDADE, COD_TIPO, PRECO_CUSTO,
  PRECO_VENDA, QTD_ESTIMADA, QTD_REAL, QTD_MINIMA, CLAS_FISC,
  IPI, PESO_LIQ
FROM PRODUTOS
WHERE ID_PRODUTO = @ID_PRODUTO;
-- Descobre qual foi o ID_PRODUTO gerado
SET @ID_PRODUTO_NOVO = SCOPE_IDENTITY();
-- Retorna para a aplicação cliente o novo ID_PRODUTO gerado
PRINT @ID_PRODUTO_NOVO;  
END

-- Testando
select * from produtos ORDER BY ID_PRODUTO DESC;

EXEC STP_COPIA_PRODUTO 2;

select * from produtos ORDER BY ID_PRODUTO DESC;






-------------
ALTER PROCEDURE STP_COPIA_PRODUTO @ID_PRODUTO INT,
                                  @ID_PRODUTO_NOVO INT OUTPUT
AS BEGIN
-- Copia o registro existente para um novo registro
INSERT INTO PRODUTOS
( COD_PRODUTO, DESCRICAO, COD_UNIDADE, COD_TIPO, PRECO_CUSTO,
  PRECO_VENDA, QTD_ESTIMADA, QTD_REAL, QTD_MINIMA, CLAS_FISC,
  IPI, PESO_LIQ )
SELECT COD_PRODUTO, DESCRICAO, COD_UNIDADE, COD_TIPO, PRECO_CUSTO,
  PRECO_VENDA, QTD_ESTIMADA, QTD_REAL, QTD_MINIMA, CLAS_FISC,
  IPI, PESO_LIQ
FROM PRODUTOS
WHERE ID_PRODUTO = @ID_PRODUTO;
-- Descobre qual foi o ID_PRODUTO gerado
SET @ID_PRODUTO_NOVO = SCOPE_IDENTITY();
END
-- Testando
DECLARE @IDPROD INT;
EXEC STP_COPIA_PRODUTO 1, @IDPROD OUTPUT;
PRINT 'NOVO PRODUTO = ' + CAST(@IDPROD AS VARCHAR(5));






--- PROCEDURE COM CURSOR

CREATE PROCEDURE STP_MALA_DIRETA
AS BEGIN
-- Declarar variável do tipo CURSOR para "percorrer" um SELECT
DECLARE CR_MALA CURSOR KEYSET 
   FOR SELECT NOME FROM CLIENTES
       WHERE ESTADO = 'MG';
-- Declarar uma variável para cada campo do cursor
DECLARE @NOME VARCHAR(50);
-- Contador de colunas
DECLARE @CONT INT;
-- Acumulador de nomes. Será usada para armazenar os 3 
-- nomes que serão gerados para cada linha do texto
DECLARE @NOMES VARCHAR(150)
-- Abrir o cursor
OPEN CR_MALA;
-- Ler a primeira linha do cursor
FETCH FIRST FROM CR_MALA INTO @NOME;
-- Enquanto não chegar no final dos dados
WHILE @@FETCH_STATUS = 0
   BEGIN
   -- "Limpar" a variável @NOMES
   SET @NOMES = '';
   -- Atribuir 1 à variável @CONT
   SET @CONT = 1;
   -- Enquanto o contador for menor ou igual a 3 E
   -- Não chegar o final dos dados do SELECT
   WHILE @CONT <= 3 AND @@FETCH_STATUS = 0
      BEGIN
      -- Alterar o nome lido acrecentando espaços
      -- de modo que fique com tamanho total 50
      SET @NOME = @NOME + SPACE( 50 - LEN( @NOME) ); 
      -- Concatenar o nome na variável @NOMES
      SET @NOMES = @NOMES + @NOME;
      -- Ler o próximo registro
      FETCH NEXT FROM CR_MALA INTO @NOME;
      -- Incrementar o contador de colunas
      SET @CONT = @CONT + 1;
      END
   -- Imprimir a variável @NOME na área de mensagens   
   PRINT @NOMES;
   END
-- Fechar o cursor
CLOSE CR_MALA;
-- Liberar memória do cursor
DEALLOCATE CR_MALA;
END

---- Testando
EXEC STP_MALA_DIRETA
